﻿/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2011/3/11 23:15:45                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_DispatchForm') and o.name = 'FK_Jci_Dis__1REFERENCE_Jci_Mad_')
alter table Jci_Dis_DispatchForm
   drop constraint FK_Jci_Dis__1REFERENCE_Jci_Mad_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVenderParts') and o.name = 'FK_Jci_Dis__7REFERENCE_Jci_Dis_')
alter table Jci_Dis_WorkingVenderParts
   drop constraint FK_Jci_Dis__7REFERENCE_Jci_Dis_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVendorEngineer') and o.name = 'FK_Jci_Dis__6REFERENCE_Jci_Dis_')
alter table Jci_Dis_WorkingVendorEngineer
   drop constraint FK_Jci_Dis__6REFERENCE_Jci_Dis_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVendorItem') and o.name = 'FK_Jci_Dis__5REFERENCE_Jci_Dis_')
alter table Jci_Dis_WorkingVendorItem
   drop constraint FK_Jci_Dis__5REFERENCE_Jci_Dis_
go


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVendorOrder') and o.name = 'FK_Jci_Dis__2REFERENCE_Jci_Dis_')
alter table Jci_Dis_WorkingVendorOrder
   drop constraint FK_Jci_Dis__2REFERENCE_Jci_Dis_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVendorOrder') and o.name = 'FK_Jci_Dis__3REFERENCE_Jci_Mad_')
alter table Jci_Dis_WorkingVendorOrder
   drop constraint FK_Jci_Dis__3REFERENCE_Jci_Mad_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Dis_WorkingVendorOrder') and o.name = 'FK_Jci_Dis__4REFERENCE_Jci_Mad_')
alter table Jci_Dis_WorkingVendorOrder
   drop constraint FK_Jci_Dis__4REFERENCE_Jci_Mad_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Jci_Mad_Product') and o.name = 'FK_Jci_Mad__REFERENCE_Jci_Mad_')
alter table Jci_Mad_Product
   drop constraint FK_Jci_Mad__REFERENCE_Jci_Mad_
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_Evaluation')
            and   type = 'U')
   drop table Jci_Dis_Evaluation
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_DispatchForm')
            and   type = 'U')
   drop table Jci_Dis_DispatchForm
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_DispatchHistory')
            and   type = 'U')
   drop table Jci_Dis_DispatchHistory
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVenderParts')
            and   type = 'U')
   drop table Jci_Dis_WorkingVenderParts
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVendorEngineer')
            and   type = 'U')
   drop table Jci_Dis_WorkingVendorEngineer
go
if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVendorFileItem')
            and   type = 'U')
   drop table Jci_Dis_WorkingVendorFileItem
go
if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVendorItem')
            and   type = 'U')
   drop table Jci_Dis_WorkingVendorItem
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVendorFileItem')
            and   type = 'U')
   drop table Jci_Dis_WorkingVendorFileItem
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Dis_WorkingVendorOrder')
            and   type = 'U')
   drop table Jci_Dis_WorkingVendorOrder
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Mad_Customer')
            and   type = 'U')
   drop table Jci_Mad_Customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Mad_Product')
            and   type = 'U')
   drop table Jci_Mad_Product
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_Company')
            and   type = 'U')
   drop table Jci_Sys_Company
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_Dictionary')
            and   type = 'U')
   drop table Jci_Sys_Dictionary
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_ErrorLog')
            and   type = 'U')
   drop table Jci_Sys_ErrorLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_OperationLog')
            and   type = 'U')
   drop table Jci_Sys_OperationLog
go

/*==============================================================*/
/* Table: Jci_Dis_DispatchForm                                  */
/*==============================================================*/


CREATE TABLE [dbo].[Jci_Dis_DispatchForm](
	[DispatchFormId] [uniqueidentifier] NOT NULL,
	[CustomerId] [uniqueidentifier] NULL,
	[ProductID] [uniqueidentifier] NULL,
	[FormCode] [varchar](512) NULL,
	[FormCategory] [int] NULL,
	[M_IssueSource] [uniqueidentifier] NULL,
	[M_MaintainType] [nvarchar](50) NULL,
	[M_Priority] [nvarchar](50) NULL,
	[M_PersonalityService] [nvarchar](50) NULL,
	[M_HQappoint] [nvarchar](50) NULL,
	[M_RepairType] [uniqueidentifier] NULL,
	[M_RepairContent] [nvarchar](50) NULL,
	[M_Remark] [nvarchar](50) NULL,
	[C_Description] [nvarchar](1024) NULL,
	[A_Description] [nvarchar](1024) NULL,

	AllocatedBranch      varchar(512)         null,
	AllocatedBranchDate datetime null,
	BranchManagerName    nvarchar(50)         null,
	BranchManagerTitle   nvarchar(50)         null,
	BranchManagerPhone   nvarchar(50)         null,
	AllocatedVendor      varchar(512)         null,
	VendorName           nvarchar(512)        null,
	VendorPhone          varchar(512)         null,
	AllocatedVendorDate  datetime null,
	
	
	[FormStatus] [int] NULL,
	[DispatchStatus] [int] NULL,
	[CreateOn] [datetime] NULL,
	[CreateBy] [varchar](80) NULL,
 CONSTRAINT [PK_Jci_Dis_DISPATCHFORM] PRIMARY KEY CLUSTERED 
(
	[DispatchFormId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

/*==============================================================*/
/* Table: Jci_Dis_DispatchHistory                               */
/*==============================================================*/
create table Jci_Dis_DispatchHistory (
   DispatchHistoryId    uniqueidentifier     not null,
   FromEntity           varchar(50)          null,
   FromEntityType       int                  null,
   ToEntity             varchar(50)          null,
   ToEntityType         int                  null,
   CreateOn             datetime             null,
   CreateBy             varchar(80)          null,
   constraint PK_Jci_Dis_DISPATCHHISTORY primary key (DispatchHistoryId)
)
go

/*==============================================================*/
/* Table: Jci_Dis_WorkingVenderParts                            */
/*==============================================================*/
create table Jci_Dis_WorkingVenderParts (
   WorkingVenderPartsId uniqueidentifier     not null,
   WorkingVendorOrderId uniqueidentifier     null,
   PartCode             varchar(50)          null,
   PartName             nvarchar(512)        null,
   PN                   varchar(512)         null,
   Count                int                  null,
   Type                 int                  null,
   Remark               text                 null,
   constraint PK_Jci_Dis_WORKINGVENDERPARTS primary key (WorkingVenderPartsId)
)
go

/*==============================================================*/
/* Table: Jci_Dis_WorkingVendorEngineer                         */
/*==============================================================*/
create table Jci_Dis_WorkingVendorEngineer (
   WorkingVendorEngineer uniqueidentifier     not null,
   WorkingVendorOrderId uniqueidentifier     null,
   EngineerName         nvarchar(50)         null,
   EngineerPhone        varchar(50)          null,
   DT                   datetime             null,
   AT                   datetime             null,
   LT                   datetime             null,
   T                    float                null,
   WS                   float                null,
   HRS                  float                null,
   TT                   float                null,
   OT                   float                null,
   CheckHour float null,
   constraint PK_Jci_Dis_WORKINGVENDORENGINE primary key (WorkingVendorEngineer)
)
go

/*==============================================================*/
/* Table: Jci_Dis_WorkingVendorItem                             */
/*==============================================================*/
create table Jci_Dis_WorkingVendorItem (
   WorkingVendorOrderOtherItemId uniqueidentifier     not null,
   WorkingVendorOrderId uniqueidentifier     null,
[JobOrderNo] [nvarchar](50) NULL,
	[UnitModel] [varchar](50) NULL,
	[FactorySerialNo] [varchar](50) NULL,
	[CompressorSerialNo] [varchar](50) NULL,
	[OilType] [varchar](50) NULL,
	[Property] [varchar](100) NULL,
	[FaultDescription] [nvarchar](1024) NULL,
	[ResolvingAction] [nvarchar](1024) NULL,
	[ServiceStatus] [nvarchar](50) NULL,
	[FaultCode] [varchar](50) NULL,
	[PartProperty] [varchar](50) NULL,
	[WarrantyClain] [varchar](50) NULL,
	[ApprovedByOfficer] [varchar](50) NULL,
	[ApprovedByFty] [varchar](50) NULL,
	[Labor] [decimal](18, 2) NULL,
	[Lodging] [decimal](18, 2) NULL,
	[Misc] [decimal](18, 2) NULL,
	[Transportation] [decimal](18, 2) NULL,
	[Material] [decimal](18, 2) NULL,
   constraint PK_Jci_Dis_WORKINGVENDORITEM primary key (WorkingVendorOrderOtherItemId)
)
go

/*==============================================================*/
/* Table: Jci_Dis_WorkingVendorOrder                            */
/*==============================================================*/
create table Jci_Dis_WorkingVendorOrder (
   WorkingVendorOrderId uniqueidentifier     not null,
   DispatchFormId       uniqueidentifier     null,
   CustomerId           uniqueidentifier     null,
   ProductId            uniqueidentifier     null,
   WorkingType          int                  null,
   CreateOn             datetime             null,
   CreateBy             varchar(80)          null,
   constraint PK_Jci_Dis_WORKINGVENDORORDER primary key (WorkingVendorOrderId)
)
go

/*==============================================================*/
/* Table: Jci_Mad_Customer                                      */
/*==============================================================*/

CREATE TABLE [dbo].[Jci_Mad_Customer](
	[CustomerId] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](1024) NULL,
	[Gender] [bit] NULL,
	[Type] [int] NULL,
	[Address] [nvarchar](1024) NULL,
	[HomePhone] [varchar](512) NULL,
	[Mobile] [varchar](512) NULL,
	[OfficePhone] [varchar](512) NULL,
	[Email] [varchar](512) NULL,
	[PostCode] [varchar](512) NULL,
	[Province] [varchar](512) NULL,
	[City] [varchar](512) NULL,
	[Country] [varchar](512) NULL,
	[AreaCode] [varchar](512) NULL,
	[Remark] [nvarchar](1024) NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_Jci_Mad_CUSTOMER] PRIMARY KEY CLUSTERED 
(
	[CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/*==============================================================*/
/* Table: Jci_Mad_Product                                       */
/*==============================================================*/

CREATE TABLE [dbo].[Jci_Mad_Product](
	[ProductId] [uniqueidentifier] NOT NULL,
	[CustomerId] [uniqueidentifier] NULL,
	[ProductCategory] [uniqueidentifier] NULL,
	[ProductType] [varchar](80) NULL,
	[BuyDate] [datetime] NULL,
	[SerialNumber] [varchar](80) NULL,
	[InvoiceCode] [varchar](50) NULL,
	[InternCode] [varchar](50) NULL,
	[OutSideCode] [varchar](50) NULL,
	[RefrigerationType] [varchar](50) NULL,
	[CardCode] [varchar](50) NULL,
	[ProductLevel] [varchar](50) NULL,
	[ProductDate] [datetime] NULL,
	[SelPhone] [varchar](50) NULL,
	[Remark] [nvarchar](1024) NULL,
	[CreateOn] [datetime] NULL,
	[CreateBy] [varchar](80) NULL,
	[ProductBrand] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Jci_Mad_PRODUCT] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


/*==============================================================*/
/* Table: Jci_Sys_Company                                       */
/*==============================================================*/
create table Jci_Sys_Company (
   CompanyId            uniqueidentifier     not null,
   CompanyCode          varchar(512)         null,
   ParentCompanyCode	varchar(512)		 null,
   Name                 nvarchar(1024)       null,
   Description			nvarchar(1024)		 null,
   Address				nvarchar(1024)		 null,
   Area					nvarchar(50)		 null,
   ConnectPersonName1	nvarchar(50)		 null,
   ConnectPersonPhone1  varchar(50)			 null,
   ZipCode1				varchar(50)			 null,
   ConnectPersonName2	nvarchar(50)		 null,
   ConnectPersonPhone2  varchar(50)			 null,
   ZipCode2				varchar(50)			 null,
   Type                 int                  null,
   constraint PK_Jci_Sys_COMPANY primary key (CompanyId)
)
go

/*==============================================================*/
/* Table: Jci_Sys_Dictionary                                    */
/*==============================================================*/
create table Jci_Sys_Dictionary (
    [DictionaryId] [uniqueidentifier] NOT NULL,
	[DictionaryType] [int] NOT NULL,
	[ParentId] [uniqueidentifier] NULL,
	[Path] [varchar](500) NOT NULL,
	[Code] [nvarchar](50) NULL,
	[Value] [nvarchar](100) NOT NULL,
	[DivisionType] [int] NULL,
	[Description] [nvarchar](4000) NULL,
	[ValueCN] [nvarchar](100) NULL,
	[Alias] [nvarchar](100) NULL,
	[SynFlag] [char](1) NOT NULL,
	[DictionaryOrder] [int] NULL,
	[IsDisabled] [int] NOT NULL,
	[InternalCode] [varchar](50) NULL,
	[IsSystem] [bit] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
   constraint PK_Jci_Sys_DICTIONARY primary key (DictionaryId)
)
go

/*==============================================================*/
/* Table: Jci_Sys_ErrorLog                                      */
/*==============================================================*/
create table Jci_Sys_ErrorLog (
   ErrorLogId           uniqueidentifier     not null,
   constraint PK_Jci_Sys_ERRORLOG primary key (ErrorLogId)
)
go

/*==============================================================*/
/* Table: Jci_Sys_OperationLog                                  */
/*==============================================================*/
create table Jci_Sys_OperationLog (
   OperationLogId       uniqueidentifier     not null,
   constraint PK_Jci_Sys_OPERATIONLOG primary key (OperationLogId)
)
go

alter table Jci_Dis_DispatchForm
   add constraint FK_Jci_Dis__1REFERENCE_Jci_Mad_ foreign key (CustomerId)
      references Jci_Mad_Customer (CustomerId)
go

alter table Jci_Dis_WorkingVenderParts
   add constraint FK_Jci_Dis__7REFERENCE_Jci_Dis_ foreign key (WorkingVendorOrderId)
      references Jci_Dis_WorkingVendorOrder (WorkingVendorOrderId)
go

alter table Jci_Dis_WorkingVendorEngineer
   add constraint FK_Jci_Dis__6REFERENCE_Jci_Dis_ foreign key (WorkingVendorOrderId)
      references Jci_Dis_WorkingVendorOrder (WorkingVendorOrderId)
go

alter table Jci_Dis_WorkingVendorItem
   add constraint FK_Jci_Dis__5REFERENCE_Jci_Dis_ foreign key (WorkingVendorOrderId)
      references Jci_Dis_WorkingVendorOrder (WorkingVendorOrderId)
go

alter table Jci_Dis_WorkingVendorOrder
   add constraint FK_Jci_Dis__2REFERENCE_Jci_Dis_ foreign key (DispatchFormId)
      references Jci_Dis_DispatchForm (DispatchFormId)
go

alter table Jci_Dis_WorkingVendorOrder
   add constraint FK_Jci_Dis__3REFERENCE_Jci_Mad_ foreign key (CustomerId)
      references Jci_Mad_Customer (CustomerId)
go

alter table Jci_Dis_WorkingVendorOrder
   add constraint FK_Jci_Dis__4REFERENCE_Jci_Mad_ foreign key (ProductId)
      references Jci_Mad_Product (ProductId)
go

alter table Jci_Mad_Product
   add constraint FK_Jci_Mad__REFERENCE_Jci_Mad_ foreign key (CustomerId)
      references Jci_Mad_Customer (CustomerId)
go


if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_Function')
            and   type = 'U')
   drop table Jci_Sys_Function
go

CREATE TABLE [dbo].[Jci_Sys_Function](
	[FuncID] [nvarchar](10) NOT NULL,
	[FFuncID] [nvarchar](10) NULL,
	[FuncName] [nvarchar](50) NULL,
	[IsMenu] [int] NULL,
	[URL] [nvarchar](250) NULL,
	[ImageUrl] [nvarchar](250) NULL,
	[Target] [nvarchar](50) NULL,
	[Description] [nvarchar](250) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_TM_SYS_FUNCTION] PRIMARY KEY CLUSTERED 
(
	[FuncID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_Role')
            and   type = 'U')
   drop table Jci_Sys_Role
go


CREATE TABLE [dbo].[Jci_Sys_Role](
	[RoleID] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](50) NULL,
	[RoleDesc] [nvarchar](512) NULL,
	[IsSystem] [int] NULL,
	[LastModifyBy] [uniqueidentifier] NULL,
	[LastModifyOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TM_Sys_Role_PK] PRIMARY KEY CLUSTERED 
(
	[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_User')
            and   type = 'U')
   drop table Jci_Sys_User
go



CREATE TABLE [dbo].[Jci_Sys_User](
	[UserID] [uniqueidentifier] NOT NULL,
	[CompanyCode] [nvarchar](50) null,
	[Account] [nvarchar](50) NULL,
	[Password] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[Email] [nvarchar](50) NULL,
	[IsConnectUser] [bit] null,
	[Level] [int] null,
	[LastModifyBy] [nvarchar](50) NULL,
	[LastModifyOn] [datetime] NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TM_Sys_Admin_PK] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_RoleFunctionRelation')
            and   type = 'U')
   drop table Jci_Sys_RoleFunctionRelation
go



CREATE TABLE [dbo].[Jci_Sys_RoleFunctionRelation](
	[ID] [uniqueidentifier] NOT NULL,
	[RoleID] [uniqueidentifier] NULL,
	[FuncID] [nvarchar](10) NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TR_Sys_RoleCodeRelation_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_UserRoleRelation')
            and   type = 'U')
   drop table Jci_Sys_UserRoleRelation
go



CREATE TABLE [dbo].[Jci_Sys_UserRoleRelation](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[RoleID] [uniqueidentifier] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [TR_Sys_AdminRoleRelation_PK] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


if exists (select 1
            from  sysobjects
           where  id = object_id('Jci_Sys_AutoCodeRule')
            and   type = 'U')
   drop table [Jci_Sys_AutoCodeRule]
go



CREATE TABLE [dbo].[Jci_Sys_AutoCodeRule](
	[AutoCodeRuleId] [varchar](50) NOT NULL,
	[RuleName] [nvarchar](100) NOT NULL,
	[RuleFormat] [varchar](100) NOT NULL,
	[ResetType] [int] NOT NULL,
	[Seed] [int] NOT NULL,
	[ResetDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BsiCodeRule] PRIMARY KEY CLUSTERED 
(
	[AutoCodeRuleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO










----------------------------------------------------Proc --- Func --- View--------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_GenId]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[proc_GenId]
go
create procedure [dbo].[proc_GenId] (@RuleId nvarchar(20)) as
BEGIN
	DECLARE @ResetDate datetime,
			@ResetType int,
			@CurrentNo int,
			@Diff_day int,	
			@Diff_Month int,
			@now datetime
    set @now = GETDATE()
	select @ResetDate = ResetDate, 
		@ResetType = ResetType,
		@CurrentNo = Seed,
		@Diff_day = datediff(dd, ResetDate, @now),
		@Diff_Month = datediff(mm, ResetDate, @now)
	from jci_sys_AutoCodeRule where AutoCodeRuleId = @RuleId

	if (@ResetType = 1) 
	begin
		if (@Diff_day > 0)
			set @CurrentNo = 0
	end
	else if (@ResetType = 30) 
	begin
	    if (@Diff_Month > 0 and DATEPART(dd, @now) = 1)
			set @CurrentNo = 0
	end

	update jci_sys_AutoCodeRule set Seed = @CurrentNo + 1, ResetDate = @now
	where AutoCodeRuleId = @RuleId
	select * from jci_sys_AutoCodeRule where AutoCodeRuleId = @RuleId

END

go

alter table Jci_Dis_WorkingVendorEngineer add [Date] datetime

go

/****** Object:  Table [dbo].[Jci_Dis_WorkingVendorFileItem]    Script Date: 03/21/2011 13:54:14 ******/
 
CREATE TABLE [dbo].[Jci_Dis_WorkingVendorFileItem](
	[ID] [uniqueidentifier] NOT NULL,
	[WorkingVendorOrderId] [uniqueidentifier] NULL,
	[FilePath] [nvarchar](128) NULL,
	[CreateTime] [datetime] NULL,
	[Deleted] [bit] NULL,
	[FileName] [nvarchar](128) NULL,
	[FileOriginalName] [nvarchar](128) NULL,
 CONSTRAINT [PK_Jci_Dis_WorkingVendorFileItem] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 
-----------------------2011-3-23 新加字段：办事处所属省份-----------------
alter table Jci_Sys_Company add Province nvarchar(50)
 
-----------------------2011-3-24 新加字段：派工单表中加办事处跟客户确定的预约时间-----------------
alter table Jci_Dis_DispatchForm add ReservationDate datetime

alter table Jci_Dis_DispatchForm add IsOnsite bit



CREATE TABLE [dbo].[Jci_Dis_Evaluation](
	[EvaluationID] [uniqueidentifier] NOT NULL,
	[TechnologyScore] [int] NULL,
	[HasChangeAccessory] [bit] NULL,
	[Accessory] [varchar](50) NULL,
	[HasReceiveFee] [bit] NULL,
	[Fee] [decimal](18, 2) NULL,
	[ServiceStandard] [int] NULL,
	[Summary] [nvarchar](3000) NULL,
	[VisitTimelyScore] [int] NULL,
	[DispatchTimelyScore] [int] NULL,
	[ServiceProviderAttitudeScore] [int] NULL,
	[DispatchFormId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Jci_Dis_Evaluation] PRIMARY KEY CLUSTERED 
(
	[EvaluationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Jci_Dis_Evaluation]  WITH CHECK ADD  CONSTRAINT [FK_Jci_Dis_Evaluation_Jci_Dis_DispatchForm] FOREIGN KEY([DispatchFormId])
REFERENCES [dbo].[Jci_Dis_DispatchForm] ([DispatchFormId])
GO

ALTER TABLE [dbo].[Jci_Dis_Evaluation] CHECK CONSTRAINT [FK_Jci_Dis_Evaluation_Jci_Dis_DispatchForm]

Go
alter table Jci_Mad_Customer add CustomerAttribute varchar(128)

GO 

 alter table  Jci_Dis_DispatchForm  add M_DiscoverDate datetime
 
 go

--工作单工时审核 
alter table Jci_Dis_WorkingVendorOrder add CheckBy varchar(50)
alter table Jci_Dis_WorkingVendorOrder add CheckDate datetime
alter table Jci_Dis_WorkingVendorOrder add IsChecked bit

go